package com.qububu.db.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.qububu.common.user.dto.UserInfoDto.Sex;
import com.qububu.db.dao.PlayArticleInfoDao;
import com.qububu.play.common.dto.ArticleAuthor;
import com.qububu.play.common.dto.ArticleDetailMsgDto;
import com.qububu.play.common.dto.ArticleDetailMsgDto.ArticleTypeMsg;
import com.qububu.play.common.dto.ArticleInfoDto;
import com.qububu.play.common.dto.ArticleInfoDto.ArticleType;
import com.qububu.utils.ParamConverter;
import com.qububu.utils.SystemUtil;

/**
 * @功能 与文章数据有关的数据库操作
 * 
 * @author LH
 * */
@Repository
public class PlayArticleInfoDaoImpl implements PlayArticleInfoDao{

	private JdbcTemplate jdbcTemplate;
	
	private int firstPageDetailDataCount = 25;
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	public List<ArticleInfoDto> getArticleListByArticleType(int type, int pageNum) {

		int onePageDataCount = 8;
		int oneceGetPageDataNum = 7;
		int onceSearchNeedDataCount = onePageDataCount * oneceGetPageDataNum;
		List<ArticleInfoDto> articleList = new ArrayList<ArticleInfoDto>();
		
//		String sql = "select * from article_info a, user_info u where article_type = ? and u.user_id = a.user_id limit 0,500";
		StringBuffer selectSql = new StringBuffer("SELECT * FROM article_info a, user_info u WHERE article_type = ? AND u.user_id = a.user_id");
//		StringBuffer limitSql = new StringBuffer(" LIMIT ").append(pageNum * onceSearchNeedDataCount).append(",").append((pageNum + 1) * onceSearchNeedDataCount);
		StringBuffer limitSql = new StringBuffer(" LIMIT ").append(pageNum * onceSearchNeedDataCount).append(",").append(onceSearchNeedDataCount);
		StringBuffer orderSql = new StringBuffer(" ORDER BY").append(" article_create_dttm").append(" DESC");
		String sql = (selectSql.append(orderSql).append(limitSql)).toString();
		List<Map<String, Object>> articleMap = jdbcTemplate.queryForList(sql, type);
		ArticleInfoDto articleInfoDto = null;
		ArticleAuthor author = null;
		for (Map<String, Object> elem : articleMap) {
			author = new ArticleAuthor((Long) elem.get("user_id"), (Long) elem.get("position_id"));
			author.setAuthorName(String.valueOf(elem.get("user_nickname")));
			author.setUserLogoUrl(String.valueOf(elem.get("user_logo_url")));
			author.setSex(Sex.getSex((Integer) elem.get("user_sex")));
			
			articleInfoDto = new ArticleInfoDto( (Long) elem.get("article_id"));
			articleInfoDto.setAuthor(author);
			articleInfoDto.setArticleTitle(String.valueOf(elem.get("article_title")));
			articleInfoDto.setArticleSummaryText(String.valueOf(elem.get("art_summary_text")));
			articleInfoDto.setArticleCreateIp(String.valueOf(elem.get("article_create_ip")));
			articleInfoDto.setArticleCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("article_create_dttm"))));
			articleInfoDto.setArtSummaryPicUrl(String.valueOf(elem.get("art_summary_pic_url")));
			articleInfoDto.setArticleType(ArticleType.getArticleType( (Integer)elem.get("article_type")));
			
			articleList.add(articleInfoDto);
		}
		
		return articleList;
	}

	public List<ArticleInfoDto> getArticleListByActionId(int type,
			String actionId, int pageNum) {
		int onePageDataCount = 8;
		int oneceGetPageDataNum = 7;
		int onceSearchNeedDataCount = onePageDataCount * oneceGetPageDataNum;
		
		StringBuffer selectSql = new StringBuffer("SELECT *");
		StringBuffer fromSql = new StringBuffer(" FROM article_info a, user_info u, action_people ap");
		StringBuffer commonWhereSql = new StringBuffer(" WHERE").append(" a.article_type=").append(type);
		commonWhereSql.append(" AND").append(" ap.people_id=a.user_id");
		commonWhereSql.append(" AND u.user_id = a.user_id");
		StringBuffer limitBuffer = new StringBuffer(" LIMIT ");
		limitBuffer.append(pageNum * onceSearchNeedDataCount).append(",").append(onceSearchNeedDataCount);
		StringBuffer orderSql = new StringBuffer(" ORDER BY").append(" article_create_dttm").append(" DESC");
		String sql = (selectSql.append(fromSql).append(commonWhereSql).append(orderSql).append(limitBuffer)).toString();
//		System.out.println("\nsql--\n"+sql +"\n\n");
		List<Map<String, Object>> articleMap = jdbcTemplate.queryForList(sql);
		List<ArticleInfoDto> articleList = new ArrayList<ArticleInfoDto>();
		ArticleInfoDto articleInfoDto = null;
		ArticleAuthor author = null;
		for (Map<String, Object> elem : articleMap) {
			author = new ArticleAuthor((Long) elem.get("user_id"), (Long) elem.get("position_id"));
			author.setAuthorName(String.valueOf(elem.get("user_nickname")));
			author.setUserLogoUrl(String.valueOf(elem.get("user_logo_url")));
			author.setSex(Sex.getSex((Integer) elem.get("user_sex")));
			
			articleInfoDto = new ArticleInfoDto( (Long) elem.get("article_id"));
			articleInfoDto.setAuthor(author);
			articleInfoDto.setArticleTitle(String.valueOf(elem.get("article_title")));
			articleInfoDto.setArticleSummaryText(String.valueOf(elem.get("art_summary_text")));
			articleInfoDto.setArticleCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("article_create_dttm"))));
			articleInfoDto.setArtSummaryPicUrl(String.valueOf(elem.get("art_summary_pic_url")));
			articleInfoDto.setArticleType(ArticleType.getArticleType((Integer)elem.get("article_type")));
			
			articleList.add(articleInfoDto);
		}
		
		return articleList;
	}
	
	/**
	 * @功能 添加文章的部分内容
	 * @param articleInfoDto 文章信息
	 * */
	public long doAddArticle(ArticleInfoDto articleInfo, ArticleDetailMsgDto articleMsg) {
		String sqlToArticleInfo = "insert into article_info(article_title, art_summary_text, art_summary_pic_url, article_create_dttm, article_create_ip, article_type, user_id) values(?, ?, ?, ?, ?, ?, ?)";
		Object[] val = {
				articleInfo.getArticleTitle(),
				articleInfo.getArticleSummaryText(),
				articleInfo.getArtSummaryPicUrl(),
				articleInfo.getArticleCreateDttm(),
				articleInfo.getArticleCreateIp(),
				articleInfo.getArticleType().toIntValue(),
				ParamConverter.convertToInteger(SystemUtil.decrypt(articleInfo.getAuthor().getAuthorId()))
		};
		jdbcTemplate.update(sqlToArticleInfo, val);
		//get the id of insert.
		Long articleId = jdbcTemplate.queryForLong("select LAST_INSERT_ID()");
		if (articleId == 0){
			articleId = jdbcTemplate.queryForLong("SELECT article_id FROM article_info WHERE user_id=? and article_create_dttm=?",
															new Object[]{articleInfo.getAuthor().getAuthorId(), articleInfo.getArticleCreateDttm()}
														);
		}
		
		String sqlToArticleInfo2 = "insert into article_detail_msg(msg_title, msg_content, msg_create_ip, msg_create_dttm, msg_type, article_id, parent_msg_id, user_id) values(?, ?, ?, ?, ?, ?, ?, ?)";
		int defaultArticleParentId = 0;
		Object[] articleDetailInfoValues = {
				articleMsg.getMsgTitle(),
				articleMsg.getMsgContent(),
				articleMsg.getMsgCreateIp(),
				articleMsg.getMsgCreateDttm(),
				articleMsg.getMsgTypeMsg().toIntValue(),
				articleId,
				defaultArticleParentId,
				ParamConverter.convertToInteger(SystemUtil.decrypt(articleMsg.getAuthor().getAuthorId()))
		};
		
		jdbcTemplate.update(sqlToArticleInfo2, articleDetailInfoValues);
		
		return articleId;
	}
	
	/**
	 * @功能 删除文章
	 * @author LH
	 * @param article_id 文章的id
	 * */
	public boolean doDeleteArticle(String articleId){
		//解密
		articleId = SystemUtil.decrypt(articleId);
		
		String fromArticleInfo = "delete from article_info where article_id=?";
		jdbcTemplate.update(fromArticleInfo, articleId);
		
		String fromMessageInfo = "delete from article_detail_msg where article_id=?";
		jdbcTemplate.update(fromMessageInfo, articleId);
		
		return true;
	}

	/**
	 * 文章一级评论分页查询
	 * */
	public List<ArticleDetailMsgDto> getArticleDetailsByMessageId(int messageId, int pageNum) {
		int onePageDataCount = 25;

		StringBuffer selectSql = new StringBuffer("SELECT *");
		StringBuffer fromSql = new StringBuffer(" FROM article_detail_msg a, user_info u");
		StringBuffer commonWhereSql = new StringBuffer(" WHERE").append(" a.parent_msg_id=").append(messageId);
		commonWhereSql.append(" AND").append(" a.user_id = u.user_id");
		
		StringBuffer limitBuffer = new StringBuffer(" LIMIT ");
//		limitBuffer.append(pageNum * onePageDataCount + firstPageDetailDataCount).append(",").append((pageNum + 1) * onePageDataCount + firstPageDetailDataCount);
		limitBuffer.append(pageNum * onePageDataCount + firstPageDetailDataCount).append(",").append(onePageDataCount);
//		StringBuffer orderSql = new StringBuffer(" ORDER BY").append(" msg_create_dttm").append(" DESC");
		String sql = (selectSql.append(fromSql).append(commonWhereSql).append(limitBuffer)).toString();
		List<ArticleDetailMsgDto> detailMsgList = new ArrayList<ArticleDetailMsgDto>();
		List<Map<String, Object>> articleMap = jdbcTemplate.queryForList(sql);
		ArticleDetailMsgDto articleDetailMsgDto = null;
		ArticleAuthor author = null;
		for (Map<String, Object> elem : articleMap) {
			
			articleDetailMsgDto = new ArticleDetailMsgDto( (Long) elem.get("msg_id"),  (Long) elem.get("article_id"), (Long) elem.get("parent_msg_id"));
			author = new ArticleAuthor((Long) elem.get("user_id"), (Long) elem.get("position_id"));
			author.setAuthorName(String.valueOf(elem.get("user_nickname")));
			author.setUserLogoUrl(String.valueOf(elem.get("user_logo_url")));
			author.setSex(Sex.getSex((Integer) elem.get("user_sex")));
			
			articleDetailMsgDto.setMsgTitle(String.valueOf(elem.get("msg_title")));
			articleDetailMsgDto.setMsgContent(String.valueOf(elem.get("msg_content")));
			articleDetailMsgDto.setMsgCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("msg_create_dttm"))));
			/*articleDetailMsgDto.setArticleTypeMsg(ArticleTypeMsg.getArticleTypeMsg( (Integer)elem.get("msg_type")));*/
			articleDetailMsgDto.setAuthor(author);
			
			detailMsgList.add(articleDetailMsgDto);
		}

		return detailMsgList;
	}

	public Map<String, Object> getFirstArticleAndSomeCommentByArticleId(
			int articleId) {
		Map<String, Object> articleDetailMap = new HashMap<String, Object>();
		
		int defaultParentArticleId = 0;

		StringBuffer selectSql = new StringBuffer("SELECT *");
		StringBuffer fromSql = new StringBuffer(" FROM article_detail_msg a, user_info u");
//		StringBuffer commonWhereSql = new StringBuffer(" WHERE ").append("msg_id = ").append(articleIdInt).append(" OR").append(" parent_msg_id = ").append(articleIdInt).append(" OR").append(" article_id = ").append(articleIdInt);
//		StringBuffer commonWhereSql = new StringBuffer(" WHERE").append(" parent_msg_id=").append(articleId).append(" OR").append(" article_id=").append(articleId);
		StringBuffer commonWhereSql = new StringBuffer(" WHERE").append(" a.article_id=").append(articleId);
		commonWhereSql.append(" AND").append(" a.user_id = u.user_id");
		StringBuffer limitBuffer = new StringBuffer(" LIMIT ");
		limitBuffer.append(0).append(",").append(firstPageDetailDataCount + 1);
		String sql = (selectSql.append(fromSql).append(commonWhereSql).append(limitBuffer)).toString();

		List<Map<String, Object>> articleMap = jdbcTemplate.queryForList(sql);
		int sqlParentId;
		List<ArticleDetailMsgDto> sonMsgList = new ArrayList<ArticleDetailMsgDto>();
		ArticleDetailMsgDto articleMsgInfo = null;
		ArticleAuthor author = null;
		
		for (Map<String, Object> elem : articleMap) {
			sqlParentId = ParamConverter.convertToInteger(String.valueOf((Long) elem.get("parent_msg_id")));
			ArticleDetailMsgDto articleDetailMsgDto = new ArticleDetailMsgDto( (Long) elem.get("msg_id"),  (Long) elem.get("article_id"),  sqlParentId);
			
			author = new ArticleAuthor((Long) elem.get("user_id"), (Long) elem.get("position_id"));
			author.setAuthorName(String.valueOf(elem.get("user_nickname")));
			author.setUserLogoUrl(String.valueOf(elem.get("user_logo_url")));
			author.setSex(Sex.getSex((Integer) elem.get("user_sex")));
			
			articleDetailMsgDto.setMsgTitle(String.valueOf(elem.get("msg_title")));
			articleDetailMsgDto.setMsgContent(String.valueOf(elem.get("msg_content")));
			articleDetailMsgDto.setMsgCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("msg_create_dttm"))));
			/*articleDetailMsgDto.setArticleTypeMsg(ArticleTypeMsg.getArticleTypeMsg( (Integer)elem.get("msg_type")));*/
			articleDetailMsgDto.setAuthor(author);
			
			if (sqlParentId == defaultParentArticleId) {//文章内容
				articleMsgInfo = articleDetailMsgDto;
			} else {//文章一级评论
				sonMsgList.add(articleDetailMsgDto);
			}
			
		}
		articleDetailMap.put("articleMsgInfo", articleMsgInfo);
		articleDetailMap.put("sonMsgList", sonMsgList);

		return articleDetailMap;
	}

	public long doAddArticleMessage(ArticleDetailMsgDto articleMessage) {
		String sql = "insert into article_detail_msg(msg_title, msg_content, msg_create_ip, msg_create_dttm, msg_type, article_id, parent_msg_id, user_id) values(?, ?, ?, ?, ?, ?, ?, ?)";
		
		Object[] articleDetailInfoValues = {
				articleMessage.getMsgTitle(),
				articleMessage.getMsgContent(),
				articleMessage.getMsgCreateIp(),
				articleMessage.getMsgCreateDttm(),
				articleMessage.getMsgTypeMsg().toIntValue(),
//				ParamConverter.convertToInteger(SystemUtil.decrypt(articleMessage.getArticleId())),
				ParamConverter.convertToInteger(SystemUtil.decrypt(articleMessage.getMsgId())),
				ParamConverter.convertToInteger(SystemUtil.decrypt(articleMessage.getMsgId())),
				ParamConverter.convertToInteger(SystemUtil.decrypt(articleMessage.getAuthor().getAuthorId()))
		};
		
		jdbcTemplate.update(sql, articleDetailInfoValues);
		
		long messageId = jdbcTemplate.queryForLong("select LAST_INSERT_ID()");
		
		return messageId;
	}

	/**
	 * 
	 * */
	public List<ArticleDetailMsgDto> getArticleInfoReplyListByMsgId(int msgId) {
		
		String sql = "SELECT * FROM article_detail_msg a, user_info u WHERE a.parent_msg_id = ? AND a.user_id = u.user_id";

		List<ArticleDetailMsgDto> articleReplyList = null;
		articleReplyList = new ArrayList<ArticleDetailMsgDto>();
		
		List<Map<String, Object>> articleMap = jdbcTemplate.queryForList(sql, msgId);
		for (Map<String, Object> elem : articleMap) {
			int sqlParentId = ParamConverter.convertToInteger(String.valueOf((Long) elem.get("parent_msg_id")));
			ArticleDetailMsgDto articleDetailMsgDto = new ArticleDetailMsgDto( (Long) elem.get("msg_id"),  (Long) elem.get("article_id"),  sqlParentId);
			
			ArticleAuthor author = new ArticleAuthor((Long) elem.get("user_id"), (Long) elem.get("position_id"));
			author.setAuthorName(String.valueOf(elem.get("user_nickname")));
			author.setUserLogoUrl(String.valueOf(elem.get("user_logo_url")));
			author.setSex(Sex.getSex((Integer) elem.get("user_sex")));
			
			articleDetailMsgDto.setMsgTitle(String.valueOf(elem.get("msg_title")));
			articleDetailMsgDto.setMsgContent(String.valueOf(elem.get("msg_content")));
			articleDetailMsgDto.setMsgCreateIp(String.valueOf(elem.get("msg_create_ip")));
			articleDetailMsgDto.setMsgCreateDttm(ParamConverter.convertToInteger(String.valueOf(elem.get("msg_create_dttm"))));
			articleDetailMsgDto.setArticleTypeMsg(ArticleTypeMsg.getArticleTypeMsg( (Integer)elem.get("msg_type")));
			articleDetailMsgDto.setAuthor(author);

			articleReplyList.add(articleDetailMsgDto);
		}
		
		return articleReplyList;
	}
}